#!/usr/bin/env bash

dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -uroot -proot -hhadoop2<<EOF
use sqlserver;
select substr(B.transaction_date,1,13),A.station_id,A.mop_name,count(A.item_quantity),sum(A.item_value)
from realtime.transaction_items_mop as A
join
(select Z.station_id,Z.transaction_id,Z.transaction_date
from
( SELECT transaction_date,transaction_id,station_id,cashier_id from realtime.transactions where substr(transaction_date,1,10)='$dt' )as Z
JOIN realtime.cashiers AS Y
on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id and Y.cashier_name = 'HHT') as B
on A.transaction_id=B.transaction_id and A.station_id=B.station_id and A.item_value > 0
group by substr(B.transaction_date,1,13),A.station_id,A.mop_name;
EOF